import { defineEventHandler, getQuery } from 'h3'
import { getPool } from '../util/mysql'
import { recordLog } from '../util/log'

export default defineEventHandler(async (event: any) => {
  const query = getQuery(event) as any
  const page = Number(query.page) || 1
  const pageSize = Number(query.pageSize) || 9
  const offset = (page - 1) * pageSize
  const search = query.search || ''

  try {
    const pool = await getPool()
    // build WHERE conditions safely without embedding 'WHERE' or 'AND' in parts
    const whereParts: string[] = ['is_deleted = 0']
    const params: any[] = []
    if (search) {
      whereParts.push('(product_name LIKE ?  OR detail LIKE ?)')
      const s = `%${search}%`
      params.push(s, s)
    }
    whereParts.push('is_sale = 1')
    const where = whereParts.length ? ' WHERE ' + whereParts.join(' AND ') : ''

    // Bind only the search parameters; LIMIT/OFFSET will be injected as validated integers
    const limit = Math.max(1, Math.floor(pageSize))
    const off = Math.max(0, Math.floor(offset))
    const [rows]: any = await pool.execute(
      `SELECT id, product_name, price, detail FROM lin_product${where} ORDER BY creation_time DESC LIMIT ${limit} OFFSET ${off}`,
      params
    )

    const [[countRows]]: any = await pool.execute(
      `SELECT COUNT(1) as c FROM lin_product${where}`,
      params
    )

    return { data: rows || [], page, pageSize, total: countRows ? Number(countRows.c) : 0 }
  } catch (err: any) {
    event.node.res.statusCode = 500
    await recordLog(event, { message: `products.get error ${String(err?.message || err)}`, userId: 0 })
    return { status: 500, message: String(err?.message || err) }
  }
})
